/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ListContentModel {
  Connection connection;
  int lastIndex;

    public int getLastIndex() {
        return lastIndex;
    }
  public ListContentModel(Connection connection) {
    this.connection = connection;
    this.lastIndex=0;
  }
  public String showAllTags() throws SQLException
  {
    String query = "SELECT type FROM tag";
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = statement.executeQuery(query);
    StringBuilder sb = new StringBuilder();
    while(rs.next())
      sb.append("<a class =tags href=\"list-content.jsp?tag="+rs.getString("type")+"\">"+rs.getString("type")+"</a>");

    return sb.toString();
  }
  private String printRelevantTags(int postid) throws SQLException
  {
    StringBuilder sb = new StringBuilder();
    String query =  "SELECT type "
            + "FROM post_tag "
            + "INNER JOIN post "
            + "ON post_tag.post_id =post.ID "
            + "INNER JOIN tag "
            + "ON tag.ID=post_tag.tag_id "
            + "WHERE post.ID = "+postid;
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = statement.executeQuery(query);
    while(rs.next())
    {
        sb.append("<a class =tags href=list-content.jsp?tag="+rs.getString("type")+">"+rs.getString("type")+"</a>");
    }
    return sb.toString();

 }
  public boolean isPostLikedByUser(int userid,int postid) throws SQLException
  {
    String query = "SELECT * FROM post_who_like "
            + "WHERE user_id='"+userid+"'"
            + "AND post_id='"+postid+"'";
      System.out.println(query);
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = statement.executeQuery(query);
      System.out.println("query: "+rs.getRow());
    return ((rs.getRow()>0)? true:false);

  }
  private String printLike(int likecount,int userid,int postid) throws SQLException
  {
    String query = "SELECT * "
            + "FROM post_who_like "
            + "WHERE user_id='"+userid+"'"
            + "AND post_id='"+postid+"'";
    System.out.println(query);
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = statement.executeQuery(query);
    rs.next();
    if(isPostLikedByUser(userid, postid))
    {
        return "<small><i id=\"content-like-"+postid+"\">You've "+rs.getString("like_or_dislike")+"'"+"d this ("+likecount +"people like this)</i></small>";
    }
    else
    {
        return  "<small><i id=\"content-like-"+postid+"\">"+likecount+" people like this</i></small>";
    }
  }
   public String generateCountListQuery(String tag,String filtermode,String sortmode, int last,int maxinpage)
  {
    String tempquery ="";
    tag =tag.replaceAll("%20", " ");
      System.out.println(tag);
    if(filtermode.isEmpty()&&sortmode.isEmpty()&&tag.isEmpty())
    {
        tempquery = "SELECT * "
                + "FROM post "
                + "JOIN user "
                + "WHERE post.poster_id=user.ID "
                + "ORDER BY post.datetime "
                + "DESC";
    }
    if(filtermode.isEmpty()&&sortmode.isEmpty()&&!tag.isEmpty())
    {

        System.out.println("masuk");
        tempquery= "SELECT * "
                + "FROM post "
                + "INNER JOIN post_tag "
                + "ON post_tag.post_id =post.ID "
                + "INNER JOIN tag "
                + "ON tag.ID=post_tag.tag_id "
                + "INNER JOIN user "
                + "ON user.ID=post.poster_id "
                + "WHERE tag.type LIKE '"+tag
                + "' GROUP BY post.ID "
                + "ORDER BY post.datetime "
                + "DESC";
        System.out.println(tempquery);
    }
    if(!filtermode.isEmpty()&&!sortmode.isEmpty()&&tag.isEmpty())
    {
        String[] temptags = tag.split(",");
        
        String notclause = "";
        String andclause = "GROUP BY post.ID";
        String tempfilter = "";
        int i = 0;
        if(filtermode.equals("AND"))
        {
            andclause +=" HAVING COUNT(DISTINCT tag.type)= "+temptags.length;
        }
        else if (filtermode.equals("NOT"))
        {
            notclause ="NOT";
        }
        while(i < temptags.length-1)
        {
            tempfilter += temptags[i]+",";i++;
        }
        tempfilter+= temptags[i];
                tempquery= "SELECT * "
                        + "FROM post "
                        + "INNER JOIN post_tag "
                        + "ON post_tag.post_id =post.ID "
                        + "INNER JOIN tag "
                        + "ON tag.ID=post_tag.tag_id "
                        + "INNER JOIN user "
                        + "ON user.ID=post.poster_id "
                        + "WHERE tag.type "+notclause
                        +" IN ("+tempfilter+")"+andclause
                        +" ORDER BY post."+sortmode
                        +" DESC";
    }
    return tempquery;
    
  }
  public String generateListQuery(String tag,String filtermode,String sortmode, int last,int maxinpage)
  {
    String tempquery ="";
    tag = tag.replaceAll("%20", " ");
    if(filtermode.isEmpty()&&sortmode.isEmpty()&&tag.isEmpty())
    {
        tempquery = "SELECT * "
                + "FROM post "
                + "JOIN user "
                + "WHERE post.poster_id=user.ID "
                + "ORDER BY post.datetime "
                + "DESC LIMIT "+ last+","+maxinpage;
    }
    if(filtermode.isEmpty()&&sortmode.isEmpty()&&!tag.isEmpty())
    {
        tempquery= "SELECT * "
                + "FROM post "
                + "INNER JOIN post_tag "
                + "ON post_tag.post_id =post.ID "
                + "INNER JOIN tag "
                + "ON tag.ID=post_tag.tag_id "
                + "INNER JOIN user "
                + "ON user.ID=post.poster_id "
                + "WHERE tag.type LIKE '"+tag
                + "' GROUP BY post.ID "
                + "ORDER BY post.datetime "
                + "DESC LIMIT "+last+","+maxinpage;
    }
    if(!filtermode.isEmpty()&&!sortmode.isEmpty()&&tag.isEmpty())
    {
        String[] temptags = tag.split(",");
        String notclause = "";
        String andclause = "GROUP BY post.ID";
        String tempfilter = "";
        int i = 0;
        if(filtermode.equals("AND"))
        {
            andclause +=" HAVING COUNT(DISTINCT tag.type)= "+temptags.length;
        }
        else if (filtermode.equals("NOT"))
        {
            notclause ="NOT";
        }
        while(i < temptags.length-1)
        {
            tempfilter += temptags[i]+",";i++;
        }
        tempfilter+= temptags[i];
                tempquery= "SELECT * "
                        + "FROM post "
                        + "INNER JOIN post_tag "
                        + "ON post_tag.post_id =post.ID "
                        + "INNER JOIN tag "
                        + "ON tag.ID=post_tag.tag_id "
                        + "INNER JOIN user "
                        + "ON user.ID=post.poster_id "
                        + "WHERE tag.type "+notclause
                        +" IN ("+tempfilter+")"+andclause
                        +" ORDER BY post."+sortmode
                        +" DESC LIMIT "+last+","+maxinpage;
    }
    return tempquery;
    
  }

  public ResultSet getResultFromQuery(String query) throws SQLException
  {
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = statement.executeQuery(query);
    return rs;
  }
  public String listPost(String query,int viewerid) throws SQLException
  {
    StringBuilder listbuilder = new StringBuilder();
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet res = statement.executeQuery(query);
    while(res.next())
    {
        String [] content_temp = res.getString("content").split("\n");
        listbuilder.append("<div class=contentbox>");
        listbuilder.append("<div class=list-image>");
        listbuilder.append("<a href=content.jsp?id=$"+res.getString(1)+">");
        if(res.getString("post_type").equals("image"))
            listbuilder.append("<img src=\"upload/"+content_temp[1]+"\" alt=kontent width=\"300\" height=\"168\" "+res.getString(1)+" />");
        else if(res.getString("post_type").equals("link"))
            listbuilder.append("<img src=konten1.png alt=dummy />");
        else
        {
            String tube_id = content_temp[0].substring(31, 42);
            listbuilder.append("<iframe width=300 height=233 src=http://www.youtube.com/embed/"+tube_id +"frameborder=0 allowfullscreen></iframe>");
        }
        listbuilder.append("</a>");
        listbuilder.append("<span class=post_tag>Tags: ");
        listbuilder.append(printRelevantTags(Integer.parseInt(res.getString(1))));
        listbuilder.append("</span>");
        listbuilder.append("</div>");
        listbuilder.append("<div class=list-description>");
        listbuilder.append("<div class=\"right-align\">");
        listbuilder.append("<a onclick=doLikeOp(\"like@").append(res.getString(1)).append("\",\"id\")><img src=like.png width=20 height=20 alt=like></a>");
        listbuilder.append("<a onclick=doLikeOp(\"dislike@").append(res.getString(1)).append("\",\"id\")><img src=dislike.png width=20 height=20 alt=dislike></a>");
        try{
        listbuilder.append(printLike(Integer.parseInt(res.getString("like_count")), viewerid, Integer.parseInt(res.getString(1))));
        }catch(Exception e){System.out.println(e.getMessage());e.printStackTrace();}
        listbuilder.append("<br/>");
        listbuilder.append("<small><i>").append(res.getString(5)).append(" people commented this post</i></small>");
        listbuilder.append("</div>");//tutup div untuk right-align
        listbuilder.append("<h3><a href=content.jsp?id=").append(res.getString(1)).append(">").append(res.getString("title")).append("</a></h3>");
        listbuilder.append("<p>").append(res.getString("content")).append("</p>");
        listbuilder.append("<p><small><i>Uploaded by ").append(res.getString("username")).append(" $post_diff_datetime_string </i></small></p>");
        listbuilder.append("</div>");//tutup div untuk list-description
        listbuilder.append("</div>");//tutup div untuk contentbox

        this.lastIndex++;
    }
      return listbuilder.toString();
  }
  
  public void doOperation(String operation) throws SQLException
  {
    String[] temp_op = operation.split("\\@");
    String res = "";
    if(temp_op[0].equals("like"))
    {
        res = "UPDATE post SET like_count=(like_count+1) WHERE ID= '"+temp_op[1]+"'";
    }
    else
    {
        res = "UPDATE post SET like_count=(like_count-1) WHERE ID= '"+temp_op[1]+"'";
    }
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    int rs = statement.executeUpdate(res);
  }
  public void insertToLikeRelation(int userid,int postid, String op) throws SQLException
  {

      String query = "INSERT INTO progin_171_13509047.post_who_like (post_id, user_id, like_or_dislike)"+
					"VALUES ('"+postid+"', '"+userid+"', '"+op+"')";
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    int rs = statement.executeUpdate(query);

  }
  public String fetchLikeQuery(int postid,int userid,String op) throws SQLException
  {
    String res =  "SELECT like_count FROM post WHERE post.ID = '"+postid+"'";
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = statement.executeQuery(res);
    try{
        insertToLikeRelation(userid, postid, op);

    return "You've "+op+"'d this ("+rs.getString("like_count")+" people like this)";
      }catch (Exception e){e.printStackTrace();System.out.println(e.getMessage());}
    rs.next();
    return rs.getString("like_count")+"people like this";
  }
  public boolean isUserOperationSame(int userid, int postid, String op) throws SQLException
  {
    String query = "SELECT * FROM post_who_like "
            + "WHERE user_id='"+userid+"'"
            + " AND post_id='"+postid+"' "
            + "AND like_or_dislike='"+op+"'";
      System.out.println(query);
      System.out.println("safjewfawef");
      System.out.println("safjewfawef");
      System.out.println("safjewfawef");
      System.out.println("safjewfawef");
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = statement.executeQuery(query);
    return (rs.getRow()>0);
  }
  public String neutralizeLikeCount(int userid,int postid, String op) throws SQLException
    {
    String query = "DELETE FROM post_who_like "
            + "WHERE user_id='"+userid+"'"
            + " AND post_id='"+postid+"'";
    if(op.equals("like"))
    {
        doOperation("dislike@"+postid);
    }
    else
    {
        doOperation("like@"+postid);
    }
    String q_like_count  = "SELECT like_count FROM post WHERE post.ID='"+postid+"'";
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = statement.executeQuery(q_like_count);
    Statement statement1 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    int res = statement1.executeUpdate(query);
    rs.next();
    return rs.getString("like_count")+"people like this";
  }

  public String getLikeOrDislike(int userid, int postid) throws SQLException
    {
    String query = "SELECT * FROM post_who_like "
            + "WHERE user_id='"+userid+"'"
            + " AND post_id='"+postid+"'";
    Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = statement.executeQuery(query);
    rs.next();
    return rs.getString("like_or_dislike");
  }
}
